﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;


public partial class T_CountPage : System.Web.UI.Page
{
    public string studentid;                      //用于保存表格修改之前的id，以便表格修改之后在数据库中找到相应行
    public static DataTable ta = new DataTable(); //用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();   //用于数据绑定
    public static string accnum;           //登录账号
    public static string role;             //登录身份


    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";                 //分页处理
        btnPrev.Text = "前一页"; 
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            accnum = (string)Session["AccNum"];
            role = (string)Session["role"];
            HideDatalist();
        }
    }

    private void ShowStats()                     //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }

    protected void LinkButton1_Click(object sender, EventArgs e)//点击首页处理
    {

    }
    protected void DL2_TextChanged(object sender, EventArgs e)
    {
        DL3.Visible = false;
        if (DL2.Text == "按班级统计")
        {
            DL3.Visible = true;
            DL3.Text = "请选择班级";
        }
    }

    void ShowDatalist()
    {
        dg1.Visible = true;
        DL1.Visible = false;
        DL2.Visible = false;
        DL3.Visible = false;
        Label1.Visible = false;
        TextBox1.Visible = false;
        Button1.Visible = false;

        btnFirst.Visible = true;
        btnPrev.Visible = true;
        btnNext.Visible = true;
        btnLast.Visible = true;
        lblCurrentIndex.Visible = true;
        lblPageCount.Visible =true;
    }
    void HideDatalist()
    {
        dg1.Visible =false;
        DL1.Visible = true;
        DL2.Visible = true;
        DL3.Visible = false;
        Label1.Visible = true;
        TextBox1.Visible = true;
        Button1.Visible = true;
        DL1.Text = "请选择学期";
        DL2.Text = "请选择统计方式";
        DL3.Text = "请选择班级";
        TextBox1.Text = "";

        btnFirst.Visible=false;   
        btnPrev.Visible=false;
        btnNext.Visible=false;
        btnLast.Visible=false;
        lblCurrentIndex.Visible=false;
        lblPageCount.Visible = false;

        Label2.Visible = false;
        TextBox2.Visible = false;
        Label3.Visible = false;
        TextBox3.Visible = false;
        Label4.Visible = false;
        TextBox4.Visible = false;
        Label5.Visible = false;
        TextBox5.Visible = false;
        Label6.Visible = false;
        TextBox6.Visible = false;
        Label2.Text = "平均分";
        Label3.Text = "一班平均分";
        Label4.Text = "二班平均分";
        Label5.Text = "三班平均分";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox4.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
    }

    /*
    void ShowControl()
    {
        DL1.Visible = true;
        DL2.Visible = true;
        DL3.Visible = false;
        Label1.Visible = true;
        TextBox1.Visible = true;
        Button1.Visible = true;
        DL1.Text = "请选择学期";
        DL2.Text = "请选择统计方式";
        DL3.Text = "请选择班级";
        TextBox1.Text = "";


    }
    void HideControl()
    {
        DL1.Visible = false;
        DL2.Visible = false;
        DL3.Visible = false;
        Label1.Visible = false;
        TextBox1.Visible = false;
        Button1.Visible = false;

    }
     */

    protected void LinkButton2_Click(object sender, EventArgs e)//点击统计查询处理
    {
        HideDatalist();
    }
    protected void Button1_Click(object sender, EventArgs e)//点击统计按钮处理函数
    {
        //HideControl();
        string sql = "";
        if (DL1.Text == "请选择学期")
        {
            Alert("请选择学期");
        }
        else
        {
            if (DL2.Text == "请选择统计方式")
            {
                Alert("请选择统计方式");
            }
            else
            {
                if (DL2.Text == "整体统计")
                {
                    if (TextBox1.Text == "")
                    {
                        Alert("请输入课程名");
                    }
                    else
                    {
                        
                        sql = "select identity(int,1,1) as rank,COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,STU_INFO.id as studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.name as semester into #aa from CLASS_INFO ,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='"+accnum+"' and CLASS_INFO.state='1' and COURSE_INFO.name='"+TextBox1.Text+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='"+DL1.Text+"' order by mark desc;select *from #aa;drop table #aa;";
                        try
                        {
                            if (Execute(sql) != -1)
                            {
                                ShowDatalist();
                                Label2.Visible = true;
                                TextBox2.Visible = true;
                                TextBox2.Text = ReaderValue("select avg(mark) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.state='1' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';");
                                Label3.Visible = true;
                                TextBox3.Visible = true;
                                TextBox3.Text = ReaderValue("select avg(mark) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and STU_INFO.class='1' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.state='1' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';");
                                Label4.Visible = true;
                                TextBox4.Visible = true;
                                TextBox4.Text = ReaderValue("select avg(mark) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and STU_INFO.class='2' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.state='1' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';");
                                Label5.Visible = true;
                                TextBox5.Visible = true;
                                TextBox5.Text = ReaderValue("select avg(mark) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and STU_INFO.class='3' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum+ "' and CLASS_INFO.state='1' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';");

                            }
                            else
                            {
                                Alert("统计课程不存在");
                                //return;
                            }

                        }
                        catch
                        {
                            Alert("统计课程不存在");
                        }
                        //缺少平均分计算
           
                    }
                }
                else if (DL2.Text == "分数段统计")
                {
                    if (TextBox1.Text == "")
                    {
                        Alert("请输入课程名");
                    }
                    else
                    {

                        sql = "select identity(int,1,1) as rank,COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,STU_INFO.id as studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.name as semester into #aa from CLASS_INFO ,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.state='1' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' order by mark desc;select *from #aa;drop table #aa;";

                        try
                        {
                            if (Execute(sql) != -1)
                            {
                                ShowDatalist();
                                Label2.Visible = true;
                                TextBox2.Visible = true;
                                Label3.Visible = true;
                                TextBox3.Visible = true;
                                Label4.Visible = true;
                                TextBox4.Visible = true;
                                Label5.Visible = true;
                                TextBox5.Visible = true;

                                Label6.Visible = true;
                                TextBox6.Visible = true;
                                Label2.Text = "60分以下";
                                Label3.Text = "60-70分";
                                Label4.Text = "70-80分";
                                Label5.Text = "80-90分";
                                TextBox2.Text = ReaderValue("select count(STU_INFO.id) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and mark<60;");
                                TextBox3.Text = ReaderValue("select count(STU_INFO.id) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and mark>=60 and mark<70;");
                                TextBox4.Text = ReaderValue("select count(STU_INFO.id) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and mark>=70 and mark<80;");
                                TextBox5.Text = ReaderValue("select count(STU_INFO.id) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and mark>=80 and mark<90;");
                                TextBox6.Text = ReaderValue("select count(STU_INFO.id) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and mark>=90 and mark<100;");
                            }
                            else
                            {
                                Alert("统计课程不存在");
                            }
                        }
                        catch
                        {
                            Alert("统计课程不存在");
                        }
                    }
                }
                else if (DL2.Text == "按班级统计")
                {
                    if (DL3.Text == "请选择班级")
                    {
                        Alert("请选择班级");
                    }
                    else
                    {
                        if (TextBox1.Text == "")
                        {
                            Alert("请输入课程名");
                        }
                        else
                        {
                            string cla = "";
                            if (DL3.Text == "一班")
                            {
                                cla = "1";
                            }
                            else if (DL3.Text == "二班")
                            {
                                cla = "2";
                            }
                            else if (DL3.Text == "三班")
                            {
                                cla = "3";
                            }


                            sql = "select identity(int,1,1) as rank,COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,STU_INFO.id as studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.name as semester into #aa from CLASS_INFO ,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.class='" + cla + "' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' order by mark desc; select *from #aa;drop table #aa;";

                            try
                            {
                                if (Execute(sql) != -1)
                                {
                                    ShowDatalist();
                                    Label2.Visible = true;
                                    TextBox2.Visible = true;
                                    TextBox2.Text = ReaderValue("select avg(mark) from CLASS_INFO,STU_INFO,COURSE_INFO,SEM_INFO where CLASS_INFO.state='1' and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.class='" + cla + "' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and COURSE_INFO.name='" + TextBox1.Text + "' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';");
                                }
                                else
                                {
                                    Alert("统计课程不存在");
                                }
                            }
                            catch
                            {
                                Alert("统计课程不存在");
                            }
                        }
                    }
                }
            }
        }
    }
    string ReaderValue(string sql)
    {
        string value="";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();
        int i = 0;
        while (myReader.Read())
        {
            value = myReader[0].ToString().Trim();
            i++;
        }
        myReader.Close();
        myConn.Close();
        return value;
    }

    int Execute(string sql)//缺少查询信息不存在的出错处理，即返回空表，（上面的执行语句都可用此函数替代）
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        if (ta.Rows.Count==0)
        {
            myConn.Close();
            return -1;
        }
        else
        {
            vi = new DataView(ta);
            dg1.DataSource = vi;
            dg1.DataBind();   
        
            myConn.Close();
            return 0;
        }
    }

　　void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
}
